package com.demodis.dao.impl;

import java.util.ArrayList;
import java.util.List;

import com.demodis.dao.ConResDaoI;
import com.demodis.domain.TblContRes;
import com.demodis.util.DBUtil;

public class ConResDaoImpl extends BaseDao implements ConResDaoI {

	@Override
	public List<TblContRes> get(TblContRes t) {
		String sql = "SELECT a.contResId, a.userId, a.addupRes, a.deteNum, a.contentId, b.projectName, b.detContent, a.detproOpt FROM TBL_CONTRES a, tbl_detcontent b WHERE a.contentId = b.contentId and userId = ?;";
		List<TblContRes> list = new ArrayList<TblContRes>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t.getUserId());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblContRes contRes = new TblContRes();
				contRes.setContResId(rs.getInt("contResId"));
				contRes.setUserId(rs.getInt("userId"));
				contRes.setAddupRes(rs.getInt("addupRes"));
				contRes.setDeteNum(rs.getInt("deteNum"));
				contRes.setContentId(rs.getInt("contentId"));
				contRes.setProjectName(rs.getString("projectName"));
				contRes.setDetContent(rs.getString("detContent"));
				contRes.setDetproOpt(rs.getString("detproOpt"));
				list.add(contRes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean modify(TblContRes t) {
		String sql = "UPDATE TBL_CONTRES SET deteNum = deteNum + 1, addupRes = addupRes + ?, detproOpt = ? WHERE contResId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getAddupRes());
			ps.setString(2, t.getDetproOpt());
			ps.setInt(3, t.getContResId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}
	
	@Override
	public boolean save(int userId) {
		String sql = "INSERT INTO TBL_CONTRES(USERID, CONTENTID, DETPROOPT) SELECT A.USERID, B.CONTENTID, B.DETPROOPTION FROM TBL_DETERPERSON A, TBL_DETCONTENT B WHERE A.POSID = B.POSID AND A.USERID = ? ;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, userId);

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i >= 1 ? true : false;
	}

	@Override
	public List<TblContRes> getAll() {
		String sql = "SELECT A.CONTRESID,A.USERID, (SUM(A.ADDUPRES) / DETENUM) ADDUPRES,B.USERREALNAME, C.GROUPNAME, E.POSNAME FROM TBL_CONTRES A, TBL_USER B, TBL_GROUP C, TBL_DETERPERSON D, TBL_POSITION E WHERE A.USERID = B.USERID AND B.GROUPID = C.GROUPID AND B.USERID = D.USERID AND D.POSID = E.POSID GROUP BY A.USERID;";
		List<TblContRes> list = new ArrayList<TblContRes>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TblContRes contRes = new TblContRes();
				contRes.setContResId(rs.getInt("contResId"));
				contRes.setUserId(rs.getInt("userId"));
				contRes.setUserRealName(rs.getString("userRealName"));
				contRes.setGroupName(rs.getString("groupName"));
				contRes.setPosName(rs.getString("posName"));
				contRes.setDetproOpt(rs.getDouble("addupRes")+"");
				list.add(contRes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<TblContRes> getFenguan(int userId) {
		String sql = "SELECT A.CONTRESID,A.USERID, (SUM(A.ADDUPRES) / DETENUM) ADDUPRES,B.USERREALNAME, C.GROUPNAME, E.POSNAME FROM TBL_CONTRES A, TBL_USER B, TBL_GROUP C, TBL_DETERPERSON D, TBL_POSITION E WHERE A.USERID = B.USERID AND B.GROUPID = C.GROUPID AND B.USERID = D.USERID AND D.POSID = E.POSID AND C.GROUPLEADERID = ? GROUP BY A.USERID;";
		List<TblContRes> list = new ArrayList<TblContRes>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				TblContRes contRes = new TblContRes();
				contRes.setContResId(rs.getInt("contResId"));
				contRes.setUserId(rs.getInt("userId"));
				contRes.setUserRealName(rs.getString("userRealName"));
				contRes.setGroupName(rs.getString("groupName"));
				contRes.setPosName(rs.getString("posName"));
				contRes.setDetproOpt(rs.getDouble("addupRes")+"");
				list.add(contRes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<TblContRes> getOneself(int groupId) {
		String sql = "SELECT A.CONTRESID,A.USERID, (SUM(A.ADDUPRES) / DETENUM) ADDUPRES,B.USERREALNAME, C.GROUPNAME, E.POSNAME FROM TBL_CONTRES A, TBL_USER B, TBL_GROUP C, TBL_DETERPERSON D, TBL_POSITION E WHERE A.USERID = B.USERID AND B.GROUPID = C.GROUPID AND B.USERID = D.USERID AND D.POSID = E.POSID AND B.GROUPID = ? GROUP BY A.USERID;";
		List<TblContRes> list = new ArrayList<TblContRes>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, groupId);
			rs = ps.executeQuery();
			while (rs.next()) {
				TblContRes contRes = new TblContRes();
				contRes.setContResId(rs.getInt("contResId"));
				contRes.setUserId(rs.getInt("userId"));
				contRes.setUserRealName(rs.getString("userRealName"));
				contRes.setGroupName(rs.getString("groupName"));
				contRes.setPosName(rs.getString("posName"));
				contRes.setDetproOpt(rs.getDouble("addupRes")+"");
				list.add(contRes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}
}
